/**
 *    This program is free software; you can redistribute it and/or modify
 *    it under the terms of the GNU General Public License as published by
 *    the Free Software Foundation; either version 2 of the License, or
 *    (at your option) any later version.
 *
 *    This program is distributed in the hope that it will be useful,
 *    but WITHOUT ANY WARRANTY; without even the implied warranty of
 *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *    GNU General Public License for more details.
 *
 *    You should have received a copy of the GNU General Public License
 *    along with this program; if not, write to the Free Software
 *    Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
 *    
 *    Linking this library statically or dynamically with other modules 
 *    is making a combined work based on this library. Thus, the terms and
 *    conditions of the GNU General Public License cover the whole
 *    combination.
 *    
 *    As a special exception, the copyright holders of this library give 
 *    you permission to link this library with independent modules to 
 *    produce an executable, regardless of the license terms of these 
 *    independent modules, and to copy and distribute the resulting 
 *    executable under terms of your choice, provided that you also meet, 
 *    for each linked independent module, the terms and conditions of the 
 *    license of that module.  An independent module is a module which 
 *    is not derived from or based on this library.  If you modify this 
 *    library, you may extend this exception to your version of the 
 *    library, but you are not obligated to do so.  If you do not wish 
 *    to do so, delete this exception statement from your version.
 *
 *    Project: Financas Pessoais
 *    Author : Igor Salles
 */
package org.fp.db;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.net.URL;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.fp.util.CSVTokenizer;
import org.fp.util.RemoveAcentos;

/**
 *
 * @author iroiro
 */
public class PopulateClassifiers {
    //tabelas de classificacao

    private static final String CATEGORIA = "tbcategories";
    private static final String SUB_CATEGORIA = "tbsubcategories";
    private static final String CLASSIFIER = "tbclassifier";
    //conexao default
    private static Connection conn = null;
    private static ResultSet rst = null;
    private static Statement stmt = null;
    private static DatabaseMetaData dmd = null;
    private static PreparedStatement pstmt = null;

    public boolean tableExists(String tableName) {
        if (tableName == null || tableName.isEmpty()) {
            return false;
        }
        try {
            conn = Connections.getInstance();
            dmd = conn.getMetaData();
            rst = dmd.getTables(null, null, tableName, null);
            if (rst.next()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException e1) {
            e1.printStackTrace();
            return false;
//        }finally{
//            return false;
        }
    }

    public void validaeCria() {
        if (!this.tableExists(CATEGORIA)) {
            this.criaTabela(CATEGORIA);
        }
        if (!this.tableExists(SUB_CATEGORIA)) {
            this.criaTabela(SUB_CATEGORIA);
        }
        if (!this.tableExists(CLASSIFIER)) {
            this.criaTabela(CLASSIFIER);
        }
    }

    public void criaTabela(String tabela) {
        if (tabela.isEmpty() || tabela == null) {
            System.out.println("Impossivel criar tabela. Nome invalido.");
            return;
        }
        if (tabela == CATEGORIA) {
            String stringSQL = "CREATE  TABLE `tbcategories` ("
                    + "`id` INTEGER PRIMARY KEY AUTOINCREMENT,"
                    + "`nome` VARCHAR(45) NULL ,"
                    + "`desc` VARCHAR(45) NULL );";
            this.executaCriacao(stringSQL);

        } else if (tabela == SUB_CATEGORIA) {
            String stringSQL = "CREATE  TABLE `tbsubcategories` ("
                    + "`id` INTEGER PRIMARY KEY AUTOINCREMENT,"
                    + "`category` INT NULL ,"
                    + "`nome` VARCHAR(45) NOT NULL ,"
                    + "`desc` VARCHAR(45) NULL );";
            this.executaCriacao(stringSQL);

        } else if (tabela == CLASSIFIER) {
            String stringSQL = "CREATE  TABLE `tbclassifier` ("
                    + "`id` INTEGER PRIMARY KEY AUTOINCREMENT,"
                    + "`categorie` INT NOT NULL ,"
                    + "`subcategorie` INT NOT NULL ,"
                    + "`nome` VARCHAR(45) NOT NULL ,"
                    + "`desc` VARCHAR(45) NULL );";

            this.executaCriacao(stringSQL);
        }
    }

    public void executaCriacao(String stringSQL) {
        try {
            conn = Connections.getInstance();
            stmt = conn.createStatement();
            stmt.execute(stringSQL);
            conn.commit();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    }

    public void insertDefaultValues() {
        //tabela categorias
        CSVTokenizer csvString;
        try {
            URL cvsPath = ClassLoader.getSystemResource("org/fp/files/categorias.csv");
            File arq = new File(cvsPath.getPath());
            FileInputStream fis = new FileInputStream(arq);
            InputStreamReader reader = new InputStreamReader(fis, "ISO-8859-1");
            BufferedReader br = new BufferedReader(reader);

            String line = "";
            ArrayList<String> lista = new ArrayList<String>();

            while ((line = br.readLine()) != null) {
                lista.add(line);
            }

            br.close();
            reader.close();
            fis.close();

            String strSQL = "CREATE TABLE IF NOT EXISTS tmptbl ("
                    + "`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
                    + "`category` VARCHAR(60) NOT NULL,"
                    + "`sub_category` VARCHAR(60) NOT NULL,"
                    + "`classify` VARCHAR(60) NOT NULL);";

            conn = Connections.getInstance();
            stmt = conn.createStatement();
            stmt.execute(strSQL);
            stmt.close();
            strSQL = "INSERT INTO tmptbl "
                    + "(category, sub_category, classify) "
                    + "VALUES(?, ?, ?);";
            pstmt = conn.prepareStatement(strSQL);
            int auxi = 0;

            for (String campo : lista) {
                csvString = new CSVTokenizer(campo, ",", CSVTokenizer.DOUBLE_QUATE);
                auxi = 0;
                while (csvString.hasMoreTokens()) {
                    auxi++;
                    pstmt.setObject(auxi, csvString.nextToken());
                }
                pstmt.addBatch();
            }
            int updates[] = pstmt.executeBatch();

            for (int i = 0; i < updates.length; i++) {
                if (updates[i] >= 0) {
                    //ok com numero de rows afetados
                    // System.out.println(""+updateCount[i]+" "+i);                    
                } else if (updates[i] == Statement.SUCCESS_NO_INFO) {
                    //ok sem numero de rows afetados
                } else if (updates[i] == Statement.EXECUTE_FAILED) {
                    //fail to execute
                    conn.rollback();
                }
            }
            
            conn.commit();
            pstmt.close();
            



        } catch (IOException e1) {
            e1.printStackTrace();
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
//            
//            String sqlCategoria = "INSERT INTO tbcategories (nome, desc) VALUES(?, ?);";
//            String sqlSelectCateg = "SELECT id FROM tbcategories WHERE nome = ?";
//            
//            String sqlSubcategoria = "INSERT INTO tbsubcategories (category, nome, desc) VALUES(?, ?, ?);";
//            String sqlSelectSubcateg = "SELECT id FROM tbsubcategories WHERE nome = ?";
//                        
//            String sqlClassifica = "INSERT INTO tbclassifier (categorie, subcategorie, nome, desc) VALUES(?, ?, ?, ?);";
//            String sqlSelectClas = "SELECT id FROM tbclassifier WHERE nome = ?";
//            
//            conn = Connections.getInstance();
//            pstmt = conn.prepareStatement(sqlCategoria);
//            
//            List<String> categoria = new ArrayList<String>();
//            List<String> sub_categoria = new ArrayList<String>();
//            List<String> classificacao = new ArrayList<String>();
//            int auxi = 0;
//            String auxStr = "";
//            String[][] auxCat = new String[4][4];
//            List nova1 = new ArrayList();
//            nova1.add(br);
//            //ArrayList<String>[] nova2 = (ArrayList<String>[])new ArrayList[10];
//            //ArrayList<String>[] data = new ArrayList[5];
//            //for(int i=0;i<data.length;i++) data[i] = new ArrayList<String>();
//            //data[0].add();data[0].get[0];
//                    
//
//            for (String campo : lista) {
//                csvString = new CSVTokenizer(campo, ",", CSVTokenizer.DOUBLE_QUATE);
//                auxi = 0;
//                while(csvString.hasMoreElements()){
//                    auxi++;
//                    if (auxi == 1){
//                        auxStr = csvString.nextToken();
//                        pstmt.setObject(1, auxStr);
//                        pstmt.setObject(2, auxStr);
//                        pstmt.execute();
//                        conn.commit();
//                        auxCat[0][0]=auxStr;
//                        pstmt = conn.prepareStatement(sqlSelectCateg);
//                        rst = pstmt.executeQuery();
//                        
//                    }                                       
//                }               
//            }
//        } catch (FileNotFoundException e1) {
//            e1.printStackTrace();
//        } catch (UnsupportedEncodingException e2) {
//            e2.printStackTrace();
//        } catch (IOException e3) {
//            e3.printStackTrace();
//        } catch(SQLException e4){
//            e4.printStackTrace();
//        }
//
//            
//        
////        String catSQL = "INSERT INTO tbcategories (nome, desc) VALUES(?, ?);";
//        try {
//            conn = Connections.getInstance();
//            pstmt = conn.prepareStatement(catSQL);
//            int validCommit = 0;
//            //3 categorias
//            pstmt.setObject(1, "Despesas");
//            pstmt.setObject(2, "Despesas e gastos.");
//            pstmt.execute();
//
//            pstmt.setObject(1, "Receitas");
//            pstmt.setObject(2, "Receitas e ganhos.");
//            pstmt.execute();
//
//            pstmt.setObject(1, "Movimentacoes");
//            pstmt.setObject(2, "Movimentacoes diversas.");
//            pstmt.execute();
//
//            conn.commit();
//
        //conn.commit();

        //stmt.executeBatch();

//            int updateCount[] = stmt.executeBatch();
//            
//            int validCommit = 0;
//            
//            for (int i=0; i < updateCount.length;i++){
//                if(updateCount[i] >= 0){
//                    //ok com numero de rows afetados
//                   // System.out.println(""+updateCount[i]+" "+i);
//                    validCommit++;
//                }else if(updateCount[i] == Statement.SUCCESS_NO_INFO){
//                    //ok sem numero de rows afetados
//                }else if(updateCount[i] == Statement.EXECUTE_FAILED){
//                    //fail to execute
//                    conn.rollback();
//                }
//            }
//            if (validCommit == 3) {
//                conn.commit();
//            }else{
//                conn.rollback();
//            }

//        } catch (SQLException e1) {
//            e1.printStackTrace();
//            try {
//                conn.rollback();
//            } catch (SQLException e3) {
//                e3.printStackTrace();
//            }
//        } catch (Exception e2) {
//            e2.printStackTrace();
//            try {
//                conn.rollback();
//            } catch (SQLException e3) {
//                e3.printStackTrace();
//            }
//        }

    }

    public void rodarDefaultTables() {
        this.validaeCria();
        this.insertDefaultValues();
    }

    public static void main(String args[]) {
        PopulateClassifiers pop = new PopulateClassifiers();
        //pop.rodarDefaultTables();
        pop.insertDefaultValues();
    }
}
